package com.zhouchao.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.zhouchao.bean.Dorm;

public class DormDao {
	/**
	 * 通过楼栋号，列出全部寝室
	 * 
	 */
	public List<Dorm> dormList(Connection con, int dormBuildId)throws Exception {
		List<Dorm> dormList = new ArrayList<Dorm>();
		StringBuffer sb = new StringBuffer("select * from t_dorm where dormBuildId="+dormBuildId);
		
		PreparedStatement pstmt = con.prepareStatement(sb.toString());
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()) {
			Dorm dorm=new Dorm();
			dorm.setDormBuildId(rs.getInt("dormBuildId"));
			dorm.setDormName(rs.getString("dormName"));
			dorm.setDormType(rs.getString("dormType"));
			dorm.setDormNumber(rs.getInt("dormNumber"));
			dorm.setDormMoney(rs.getFloat("dormMoney"));
			dorm.setDormTel(rs.getString("dormTel"));
			dorm.setDormSD(rs.getFloat("dormSD"));
			dormList.add(dorm);
		}
		return dormList;
	}
	public int dormNumSum(Connection con, int dormBuildId,String dormName) throws SQLException{
		String sql="select  dormNumber  from  t_dorm where dormBuildId="+dormBuildId+" and dormName='"+dormName+"';";
		System.out.println(sql);
		Statement ps = con.createStatement();
		ResultSet rs = ps.executeQuery(sql);
		while(rs.next()){
		return rs.getInt("dormNumber");
		}
			return -100;
		
	}
	/**
	 * 连接学生表，寝室表，列出寝室空余床位
	 * @throws SQLException 
	 * 
	 */
	public int dormNum(Connection con,int dormBuildId,String dormName) throws SQLException{
		String sql="select count(*)  from t_student where dormName='"+dormName+"' and dormBuildId="+dormBuildId+";";
		System.out.println(sql);
		try
		{
		//把你认为可能发生异常的代码段放在这里
			PreparedStatement pstmt = con.prepareStatement(sql);
		//	Statement ps = con.createStatement();
			//ResultSet rs = ps.executeQuery(sql);
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()){
				int num=dormNumSum(con,dormBuildId,dormName)-rs.getInt(1);
				System.out.println("num==num"+num);
				return  num;
			}
		}catch(NullPointerException e)
		{
		System.out.println("发生异常的原因为   :"+e.getMessage()+"dsddd");
		}
		return -100;
	}
	
}
